"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 智能运维ai_indexadv模块
Case Name   : 组合使用使用where条件,比较运算符,order by排序,inner join...
              using,模糊查询like,范围查询in进行查询索引推荐
Create At   : 2023/02/10
Owner       : @zou_jialiang050
Description :
    1.建表1
    2.建表2
    3.建存储过程1
    4.建存储过程2
    5.向表1,表2中插入100000条数据并统计数据的数量
    6.组合使用使用where条件,比较运算符,order by排序,inner join...using,模糊查询
      like,范围查询in进行查询索引推荐
    7.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.插入数据成功,返回表1,表2数据量
    6.返回推荐的索引列
    7.成功
History     :
    modified by @zou_jialiang050 2023/02/18:优化索引推荐断言
"""

import os
import unittest
from yat.test import Node
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class AI(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info("-----------this is setup-----------")
        self.logger.info(f"-----{os.path.basename(__file__)} start-----")
        self.dbuserNode = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Con = Constant()
        self.table1 = 't_procedure_insert_0007_01'
        self.table2 = 't_procedure_insert_0007_02'
        self.procedure1 = 'p_procedure_insert_0007_01'
        self.procedure2 = 'p_procedure_insert_0007_02'

    def test_ai_virtual_index(self):
        step = '--step1:建表1 expect:成功--'
        self.logger.info(step)
        create_table1 = self.primary_sh.execut_db_sql(f'''
            drop table if exists {self.table1};
            create table {self.table1}(col_int int,col_numeric numeric,
            col_float float,col_char char(10),col_text text,col_time time);''')
        self.logger.info(create_table1)
        self.assertIn(self.Con.TABLE_DROP_SUCCESS, create_table1,
                      "建表1失败" + step)
        self.assertIn(self.Con.TABLE_CREATE_SUCCESS, create_table1,
                      "建表1失败" + step)

        step = '--step2:建表2 expect:成功--'
        self.logger.info(step)
        create_table2 = self.primary_sh.execut_db_sql(f'''
            drop table if exists {self.table2};
            create table {self.table2}(col_int int,col_dec dec,col_money 
            money,col_boolean boolean,col_char char(10),col_clob clob);''')
        self.logger.info(create_table2)
        self.assertIn(self.Con.TABLE_DROP_SUCCESS, create_table2,
                      "建表1失败" + step)
        self.assertIn(self.Con.TABLE_CREATE_SUCCESS, create_table2,
                      "建表2失败" + step)

        step = '--step3:建存储过程1 expect:成功--'
        self.logger.info(step)
        create_procedure1 = self.primary_sh.execut_db_sql(f'''
            create or replace procedure {self.procedure1} (a int) is
            V_int int;
            V_numeric numeric;
            V_float float;
            V_char char(10);
            V_text text;
            V_time time;
            begin
            for i in 1..a
            loop
            V_int :=i;
            V_numeric :=i+1.11;
            V_float :=i*5.55;
            V_char :='x_'|| i;
            V_text :='V_text_'|| i;
            V_time :='19:41:20';
            execute immediate 'insert into {self.table1} values
            (:p1,:p2,:p3,:p4,:p5,:p6)
            'using V_int,V_numeric,V_float,V_char,V_text,V_time;
            end loop;
            end;''')
        self.logger.info(create_procedure1)
        self.assertIn(self.Con.CREATE_PROCEDURE_SUCCESS_MSG,
                      create_procedure1, "建存储过程1失败" + step)

        step = '--step4:建存储过程2 expect:成功--'
        self.logger.info(step)
        create_procedure2 = self.primary_sh.execut_db_sql(f'''
            create or replace procedure {self.procedure2}() is
            V_int int;
            V_dec dec;
            V_money money;
            V_boolean boolean;
            V_char char(10);
            V_clob clob;
            begin
            for i in 2000..100000
            loop
            V_int :=i;
            V_dec :=i*8.88;
            V_money :=i*2;
            V_boolean :='t';
            V_char :=i ||'_x';
            V_clob :='V_clob_'|| i;
            execute immediate 'insert into {self.table2} values
            (:p1,:p2,:p3,:p4,:p5,:p6)
            'using V_int,V_dec,V_money,V_boolean,V_char,V_clob;
            end loop;
            end;''')
        self.logger.info(create_procedure2)
        self.assertIn(self.Con.CREATE_PROCEDURE_SUCCESS_MSG,
                      create_procedure2, "建存储过程2失败" + step)

        step = 'step5:向表1,表2中插入100000条数据并统计数据的数量 ' \
               'expect:插入数据成功,返回表1,表2数据量'
        self.logger.info(step)
        insert_data = self.primary_sh.execut_db_sql(f'''
            call {self.procedure1}(100000);call {self.procedure2}();
            select count(*) from {self.table1};select count(*) from 
            {self.table2};''')
        self.logger.info(insert_data)
        self.assertEqual(str(insert_data.splitlines()[-7]).strip(), '100000',
                         "执行失败" + step)
        self.assertEqual(str(insert_data.splitlines()[-2]).strip(), '98001',
                         "执行失败" + step)

        step = '--step6:组合使用使用where条件,比较运算符,order by排序,inner ' \
               'join...using,模糊查询like,范围查询in进行查询索引推荐' \
               'expect:返回推荐的索引列--'
        self.logger.info(step)
        index_advise = self.primary_sh.execut_db_sql(f'''
            select * from gs_index_advise('select {self.table1}.col_int,
            {self.table1}.col_numeric,{self.table2}.col_money from 
            {self.table1} inner join {self.table2} using(col_int) where 
            {self.table1}.col_time=''19:41:20'' order by {self.table1}.col_int 
            desc limit 20;');''')
        self.logger.info(index_advise)
        str1 = f'public | {self.table1} | col_int'
        str2 = f'public | {self.table2} | col_int'
        self.assertTrue(str1 in index_advise and str2 in index_advise or str1
                        in index_advise and str2 not in index_advise,
                        "执行失败" + step)

        index_advise = self.primary_sh.execut_db_sql(
            f'''select * from gs_index_advise('select {self.table1}.col_int,
            {self.table1}.col_numeric,{self.table2}.col_money from 
            {self.table1} inner join {self.table2} using(col_int) where 
            {self.table1}.col_int > 20000 order by {self.table1}.col_int 
            desc limit 20;');''')
        self.logger.info(index_advise)
        str1 = f'public | {self.table1} | col_int'
        str2 = f'public | {self.table2} | col_int'
        self.assertTrue(str1 in index_advise and str2 in index_advise or str1
                        in index_advise and str2 not in index_advise,
                        "执行失败" + step)

        index_advise = self.primary_sh.execut_db_sql(
            f'''select * from gs_index_advise('select {self.table1}.col_int,
            {self.table1}.col_numeric,{self.table2}.col_money from 
            {self.table1} inner join {self.table2} using(col_int) where 
            {self.table1}.col_time=''19:41:20'' and {self.table1}.col_char 
            in (''x_10000'', ''x_1000000'') order by {self.table1}.col_int 
            desc limit 20;');''')
        self.logger.info(index_advise)
        str1 = f'public | {self.table1} | col_int'
        str2 = f'public | {self.table2} | col_int'
        self.assertTrue(str1 in index_advise and str2 in index_advise or str1
                        in index_advise and str2 not in index_advise,
                        "执行失败" + step)

        index_advise = self.primary_sh.execut_db_sql(
            f'''select * from gs_index_advise('select {self.table1}.col_int,
            {self.table1}.col_numeric,{self.table2}.col_money from 
            {self.table1} inner join {self.table2} using(col_int) where 
            {self.table2}.col_int < 3000 and {self.table2}.col_money in 
            (''$5,998.00'', ''$5,960.00'',''$5,982.00'') order by 
            {self.table1}.col_int desc limit 20;');''')
        self.logger.info(index_advise)
        str1 = f'public | {self.table1} | col_int'
        str2 = f'public | {self.table2} | col_int'
        self.assertTrue(str1 in index_advise and str2 in index_advise or str1
                        in index_advise and str2 not in index_advise,
                        "执行失败" + step)

        index_advise = self.primary_sh.execut_db_sql(
            f'''select * from gs_index_advise('select {self.table1}.col_int,
            {self.table1}.col_numeric,{self.table2}.col_money from 
            {self.table1} inner join {self.table2} using(col_int) where 
            {self.table1}.col_time=''19:41:20'' or {self.table1}.col_char in 
            (''x_10000'', ''x_1000000'') order by {self.table1}.col_int 
            desc limit 20;');''')
        self.logger.info(index_advise)
        str1 = f'public | {self.table1} | col_int'
        str2 = f'public | {self.table2} | col_int'
        self.assertTrue(str1 in index_advise and str2 in index_advise or str1
                        in index_advise and str2 not in index_advise,
                        "执行失败" + step)

        index_advise = self.primary_sh.execut_db_sql(
            f'''select * from gs_index_advise('select {self.table1}.col_int,
            {self.table1}.col_numeric,{self.table2}.col_money from 
            {self.table1} inner join {self.table2} using(col_int) where 
            {self.table2}.col_int < 3000 or {self.table2}.col_money in 
            (''$5,998.00'', ''$5,960.00'',''$5,982.00'') order by 
            {self.table1}.col_int desc limit 20;')''')
        self.logger.info(index_advise)
        str1 = f'public | {self.table1} | col_int'
        str2 = f'public | {self.table2} | col_int'
        self.assertTrue(str1 in index_advise and str2 in index_advise or str1
                        in index_advise and str2 not in index_advise,
                        "执行失败" + step)

        index_advise = self.primary_sh.execut_db_sql(
            f'''select * from gs_index_advise('select {self.table1}.col_int,
            {self.table1}.col_numeric,{self.table2}.col_money from 
            {self.table1} inner join {self.table2} using(col_int) where 
            {self.table1}.col_text like ''V_text_2999%'' order by 
            {self.table1}.col_int desc limit 20;');''')
        self.logger.info(index_advise)
        str1 = f'public | {self.table1} | col_int'
        str2 = f'public | {self.table2} | col_int'
        str3 = f'public | {self.table1} | col_int,col_text'
        self.assertTrue(str1 in index_advise and str2 in index_advise or str3
                        in index_advise and str1 not in index_advise and str2
                        not in index_advise, "执行失败" + step)

        index_advise = self.primary_sh.execut_db_sql(
            f'''select * from gs_index_advise('select {self.table1}.col_int,
            {self.table1}.col_numeric,{self.table2}.col_money from 
            {self.table1} inner join {self.table2} using(col_int) where 
            {self.table2}.col_dec like ''%.88'' order by {self.table1}.col_int 
            desc limit 20;');''')
        self.logger.info(index_advise)
        str1 = f'public | {self.table1} | col_int'
        str2 = f'public | {self.table2} | col_int'
        self.assertTrue(str1 in index_advise and str2 in index_advise or str1
                        in index_advise and str2 not in index_advise,
                        "执行失败" + step)

    def tearDown(self):
        step = '--step7:清理环境 expect:成功--'
        self.logger.info(step)
        result = self.primary_sh.execut_db_sql(
            f'''drop procedure {self.procedure1};
            drop procedure {self.procedure2};
            drop table {self.table1} cascade;
            drop table {self.table2} cascade;''')
        self.logger.info(result)
        self.assertEqual(result.count(self.Con.TABLE_DROP_SUCCESS),
                         2, "执行失败" + step)
        self.assertIn(self.Con.DROP_PROCEDURE_SUCCESS_MSG, result,
                      "执行失败" + step)
        self.assertEqual(result.count(self.Con.DROP_PROCEDURE_SUCCESS_MSG), 2,
                         "执行失败" + step)
        self.logger.info(f"-----{os.path.basename(__file__)} end-----")
